# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from scrapy.pipelines.images import ImagesPipeline
from twisted.enterprise import adbapi
from MySQLdb.cursors import DictCursor
import codecs
import json
import MySQLdb


class ArticlespiderPipeline:
    # def open_spider(self, spider):
    #     self.f = open("books.json", "w", encoding="utf-8")

    def process_item(self, item, spider):
        return item

    # def close_spider(self, spider):
    #     self.f.close()


class JsonWithEncodingPipeline:
    # 自定义json文件导出
    def open_spider(self, spider):
        self.fp = open("article2.json", "w", encoding="utf-8")
        # self.file = codecs.open("article.json", "w", encoding="utf-8")

    def process_item(self, item, spider):
        # lines = json.dumps(dict(item), ensure_ascii=False)
        # self.file.write(lines)
        self.fp.write(str(item))
        return item

    def close_spider(self, spider):
        self.fp.close()
        # self.file.close()

# 同步写入mysql（默认不执行）
class MysqlPipeline(object):
    def open_spider(self, spider):
        self.conn = MySQLdb.connect("localhost", "root", "123456", "article_spider", charset="utf8", use_unicode=True)
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        insert_sql = """
            insert into jobbole_article(title,url,url_object_id,front_image_path,front_image_url,parise_nums,comment_nums,fav_nums,tags,content,create_date)
            values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE parise_nums=VALUES(parise_nums)
        """
        params = list()
        params.append(item.get("title", ""))
        params.append(item.get("url", ""))
        params.append(item.get("url_object_id", ""))
        params.append(item.get("front_image_path", ""))
        asa = item.get("front_image_path")
        if isinstance(item.get("front_image_path"), list):
            front_image = ",".join(item.get("front_image_path"), [])
        params.append(item.get("front_image_path"))
        params.append(item.get("parise_nums", 0))
        params.append(item.get("comment_nums", 0))
        params.append(item.get("fav_nums", 0))
        params.append(item.get("tags", ""))
        params.append(item.get("content", ""))
        params.append(item.get("create_date", "1970-07-01"))
        self.cursor.execute(insert_sql, tuple(params))
        self.conn.commit()
        return item

# 异步写入mysql
class MysqlTwistedPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
            host=settings["MYSQL_HOST"],
            db=settings["MYSQL_DBNAME"],
            user=settings["MYSQL_USER"],
            passwd=settings["MYSQL_PASSWORD"],
            charset="utf8",
            cursorclass=DictCursor,
            use_unicode=True,
        )
        dbpool = adbapi.ConnectionPool("MySQLdb", **dbparms)
        return cls(dbpool)
        pass

    def process_item(self, item, spider):
        # 将item传到doinsert方法中
        query = self.dbpool.runInteraction(self.do_insert, item)
        # 将报错信息打印
        query.addErrback(self.handle_error)

    def handle_error(self, failure, item, spider):
        print(failure)

    # adbapi会自动传入curcor
    def do_insert(self, cursor, item):
        # ON DUPLICATE KEY UPDATE   如果数据冲突的话会更新一个字段
        insert_sql = """
                   insert into jobbole_article(title,url,url_object_id,front_image_path,front_image_url,parise_nums,comment_nums,fav_nums,tags,content,create_date)
                   values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE parise_nums=VALUES(parise_nums)
               """
        params = list()
        params.append(item.get("title", ""))
        params.append(item.get("url", ""))
        params.append(item.get("url_object_id", ""))
        params.append(item.get("front_image_path", ""))
        front_image = item.get("front_image_path")
        if isinstance(item.get("front_image_path"), list):
            front_image = ",".join(item.get("front_image_path"), [])
        params.append(front_image)
        params.append(item.get("parise_nums", 0))
        params.append(item.get("comment_nums", 0))
        params.append(item.get("fav_nums", 0))
        params.append(item.get("tags", ""))
        params.append(item.get("content", ""))
        params.append(item.get("create_date", "1970-07-01"))
        cursor.execute(insert_sql, tuple(params))


# 图片路径
class ArticleImagePipeline(ImagesPipeline):
    def item_completed(self, results, item, info):
        if "front_image_url" in item:
            image_file_path = ""
            for ok, value in results:
                image_file_path = value["path"]
            item["front_image_path"] = image_file_path
        return item
